﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class BranchDevice
    {
        public List<BranchDeviceInfo> GetList(string keyword)
        {
            List<BranchDeviceInfo> result = new List<BranchDeviceInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string search = string.Format("%{0}%", keyword).Replace("'", "''");

            string mysql = string.Format(@"SELECT BD.IMEI, BD.BRANCH_ID, B.BRANCH_NAME, BD.ENTRY_BY, BD.ENTRY_DATE, BD.UPDATE_BY, BD.UPDATE_DATE
                                           FROM TBL_MS_BRANCH_DEVICE BD INNER JOIN TBL_MS_BRANCH B ON BD.BRANCH_ID = B.BRANCH_ID WHERE B.BRANCH_NAME LIKE @KEYWORD OR BD.BRANCH_ID LIKE @KEYWORD OR BD.IMEI LIKE @KEYWORD", search);

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@KEYWORD", DbType.String, search);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    BranchDeviceInfo info = Create(mydr);
                    info.BranchName = mydr.GetString(mydr.GetOrdinal("BRANCH_NAME"));
                    result.Add(info);
                }
                mydr.Close();
            }
            return result;
        }        

        private BranchDeviceInfo Create(IDataReader dr)
        {
            BranchDeviceInfo info = new BranchDeviceInfo();            
            info.IMEI = dr.GetString(dr.GetOrdinal("IMEI"));
            info.BranchID = dr.GetString(dr.GetOrdinal("BRANCH_ID"));
            info.EntryBy = dr.GetString(dr.GetOrdinal("ENTRY_BY"));
            info.EntryDate = dr.GetDateTime(dr.GetOrdinal("ENTRY_DATE"));
            info.UpdateBy = dr.GetString(dr.GetOrdinal("UPDATE_BY"));
            info.UpdateDate = dr.GetDateTime(dr.GetOrdinal("UPDATE_DATE"));

            return info;

        }

        public BranchDeviceInfo Get(string imei)
        {
            BranchDeviceInfo info = null;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT IMEI, BRANCH_ID, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                           FROM TBL_MS_BRANCH_DEVICE WHERE IMEI = @IMEI";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);            
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, imei);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                }
                mydr.Close();
            }
            return info;
        }

        /*
        public BranchDeviceInfo GetByIMEI(string IMEI)
        {
            BranchDeviceInfo info = null;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT BRANCH_ID, IMEI, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                           FROM TBL_MS_BRANCH_DEVICE WHERE IMEI = @IMEI";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, IMEI);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                }
                mydr.Close();
            }
            return info;
        }*/

        

        public int Save(BranchDeviceInfo info)
        {         
            Database mydb = DBConnection.CreateDatabase();

            DbCommand mycomm = mydb.GetSqlStringCommand(@"
                       	    UPDATE TBL_MS_BRANCH_DEVICE_TMP SET 
                                BRANCH_ID = @BRANCH_ID,                                
                                ENTRY_BY = @ENTRY_BY,
                                ENTRY_DATE = @ENTRY_DATE,
                                UPDATE_BY = @UPDATE_BY,
                                UPDATE_DATE = @UPDATE_DATE,
                                ACT_TYPE = @ACT_TYPE
                                WHERE IMEI = @IMEI
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_BRANCH_DEVICE_TMP(IMEI,BRANCH_ID,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE) 
                            VALUES (@IMEI,@BRANCH_ID,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@ACT_TYPE)");

            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, info.IMEI);
            mydb.AddInParameter(mycomm, "@BRANCH_ID", DbType.String, info.BranchID);            
            mydb.AddInParameter(mycomm, "@ENTRY_BY", DbType.String, info.EntryBy);
            mydb.AddInParameter(mycomm, "@ENTRY_DATE", DbType.DateTime, info.EntryDate);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, info.UpdateBy);
            mydb.AddInParameter(mycomm, "@UPDATE_DATE", DbType.DateTime, info.UpdateDate);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, info.ActType);            

            return mydb.ExecuteNonQuery(mycomm);
        }

        public List<BranchDeviceInfo> GetListApproval(string UPDATE_BY)
        {
            List<BranchDeviceInfo> result = new List<BranchDeviceInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT BD.IMEI, BD.BRANCH_ID, B.BRANCH_NAME, BD.ENTRY_BY, BD.ENTRY_DATE, BD.UPDATE_BY, BD.UPDATE_DATE, BD.ACT_TYPE
                                           FROM TBL_MS_BRANCH_DEVICE_TMP BD INNER JOIN TBL_MS_BRANCH B ON BD.BRANCH_ID = B.BRANCH_ID WHERE BD.UPDATE_BY <> @UPDATE_BY";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, UPDATE_BY);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    BranchDeviceInfo info = Create(mydr);
                    info.BranchName = mydr.GetString(mydr.GetOrdinal("BRANCH_NAME"));
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));                    
                    result.Add(info);
                }
                mydr.Close();
            }
            return result;
        }        

        public void Approve(string imei, string ACT_TYPE)
        {
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetStoredProcCommand("APPROVAL_BRANCH_DEVICE");            
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, imei);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ACT_TYPE);

            mydb.ExecuteNonQuery(mycomm);
        }

        public void Reject(string imei)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE FROM TBL_MS_BRANCH_DEVICE_TMP
                             WHERE IMEI = @IMEI";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);            
            mydb.AddInParameter(mycmd, "@IMEI", DbType.String, imei);

            mydb.ExecuteNonQuery(mycmd);
        }

        public BranchDeviceInfo GetApprovalByID(string imei)
        {
            BranchDeviceInfo info = new BranchDeviceInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT IMEI, BRANCH_ID, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE
                                           FROM TBL_MS_BRANCH_DEVICE_TMP WHERE IMEI = @IMEI";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);            
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, imei);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));                    
                }
                mydr.Close();
            }
            return info;
        }
    }
}
